#!/usr/bin/env python3
# Simple Flat Table Initial GUI
###??? Why is the name not flattableeditor?
###??? We need to add the legal stuff here - licnences type, TEN3 owns, etc.
#
# Prerequisite: Initial TEN3 Python/SQLite programming course.
# This program is part of a TEN3 course. See ??? for an explanation. 
###???We need to decide what to do with pointing them somewhere to learn how we
###???are using this. We need to make a preposal to Joe to be sure it fits with 
###???the communication plan.

'''
GUI flat table editor. Allows you to open a database and choose a table to
edit. The table is flat, which means it does not have foreign keys, in other
words it has no 1-to-1 relationships. We also assume it has not 1-to-Many
relationship. All the relevant data is in one table. This is a step in
the process of helping the student understand working with the GUI and
the database. 
'''

import tkinter
from tkinter import messagebox
from tkinter import ttk
from tkinter import filedialog
from collections import OrderedDict
import databaseinterface as dbinter
import ntpath
import validate

class DBgui():
    '''
    The GUI class that sets up the initial window with buttons to choose
    the actions the user wants to perform on the database.

    We will render the actual editing buttons inactive until a connection is
    established with the database.
    '''
    def __init__(self):
        '''
        This establishes the inital window and appropriate buttons.
        '''
        self.db = None
        self.file = None
        self.connected = False
        self.selectedTable = ""
        self.root = tkinter.Tk()
        self.root.title('Flat Table Editor')
        self.root.geometry('+100+100')
        self.root.protocol("WM_DELETE_WINDOW",lambda: self.close())
        self.frame = ttk.Frame(self.root,borderwidth=3,\
                               relief='sunken')
        self.frame.grid(column=3,row=5)

        self.buttonOpen = ttk.Button(self.frame,text='Open DB')
        self.buttonOpen.grid(column=1,row=1,padx=3,pady=3)
        self.buttonOpen.bind('<Button-1>',lambda event:self.openDB())

        # Using 'command' instead of the method bind() enables us to actually
        # disable the button - bind() won't allow the button to be disabled
        self.buttonSelectTb = ttk.Button(self.frame,text='Select Table',\
                                         state='disabled',command=self.selectTable)
        self.buttonSelectTb.grid(column=2,row=1,padx=3,pady=3)

        self.buttonAdd = ttk.Button(self.frame,text='Add Record',state='disabled',\
                                    command=self.add)
        self.buttonAdd.grid(column=1,row=3,padx=3,pady=3)

        self.buttonView = ttk.Button(self.frame,text='View/Change/Delete Record',\
                                     state='disabled',command=self.selectRow)
        self.buttonView.grid(column=1,columnspan=2,row=2,padx=3,pady=3)

        self.buttonExport = ttk.Button(self.frame,text='Export',state='disabled',\
                                       command=self.export)
        self.buttonExport.grid(column=2,row=3,padx=3,pady=3)

        self.buttonClose = ttk.Button(self.frame,text='Close')
        self.buttonClose.grid(column=2,row=4,padx=3,pady=3)
        self.buttonClose.bind('<Button-1>',lambda event:self.close())

        """
        Setting the state of a button to 'disabled' greys out the button and it
        can then be enabled when we are ready to use it. But binding the left
        mouse button to the button overrides it being disabled. Therefore, for buttons
        we want disabled until we are ready for them, we use the 'command' option to
        tie that button to a function when the button is pressed.
        """
       

    def openDB(self):
        ###???George, please use convention described on our convention page of having 
        ###a single first line. This is a python convention so automatic documentation
        ###will work. 
        '''
        Method to select and connect to a SQLite database
        '''
        if self.connected:
            if not messagebox.askokcancel("Database already open!",\
                                   "Are you sure you want to close " +\
                                   self.file + " and open a new database?"):
                return # because they do not want to open a new database
            # close current db and clean up screen
            self.db.closeDB()
            self.connected = False
            self.buttonSelectTb.state(['disabled'])
            self.buttonAdd.state(['disabled'])
            self.buttonView.state(['disabled'])
            self.buttonExport.state(['disabled'])
        
        #set up database connection 
        self.db = dbinter.Database() 
        filename = filedialog.askopenfilename(title="Choose a database")
        try:
            self.connected = self.db.openDB(filename)
        except dbinter.InvalidDatabaseException:
            messagebox.showinfo(message='Not a valid database')
        else:
            # split out the name of the file without the path
            self.file = self.fileFromPath(filename)
            messagebox.showinfo(message='Opened ' + self.file + ' successfully')
            # enable the Select Table button
            self.buttonSelectTb.state(['!disabled'])
        """
        Since it is possible the user has already opened a database file and
        established a connection, we check for that first. If a connection already
        exists, we need to let the user know what database is open and ask if
        s/he wants to close it to open a new one. We need to close an open connection
        so there will be no open connections when we attempt another. We also set
        self.connected back to false so that in case the attempt doesn't work, we
        will know we have no connection established. Since we closed the connection
        there is no table open and we also disable the buttons that act on a table.
        
        We instantiate a DB object using the Database class. See our database class 
        (in databaseinterface.py) for how we determine if it is a good database or
        not.  
        
        If it is, we establish the connection. Then we pull out the filename without 
        the path so we can tell the user s/he has opened the file successfully and 
        query the user if s/he attempts to open another database.
        """

    def fileFromPath(self,path):
        '''
        Splits off the file name (without the path) regardless of OS.
        '''
        head, tail = ntpath.split(path)
        return tail or ntpath.basename(head)
    
        """
        This method uses the ntpath module to split off the filename from the path.
        It will also remove a trailing slash. This will work in Linux or Windows.
        
        ###??? Bit of help George - what is the "or" doing in the return statement?
        """

    def selectTable(self):
        '''
        Select a table from the database.
        '''
        # get a list of the tables in the database
        tblList = self.db.listTables()
        
        # display the list in a window and allow selection of a table
        selectTableWin = tkinter.Toplevel(self.frame)
        selectTableWin.geometry('250x60+100+100')
        selectTableWin.title('Select Table')
        selectTableLbl = ttk.Label(selectTableWin,text="Select a table")
        selectTableLbl.grid(column=0,row=1,sticky="E",padx=3,pady=3)
        selectTable = ttk.Combobox(selectTableWin,width=15,\
                                   state='readonly')
        selectTable['values'] = tblList
        selectTable.current(0)
        selectTable.grid(column=1,row=1,padx=3,pady=3)
        submitButton = ttk.Button(selectTableWin,text='Submit')
        submitButton.grid(columnspan=2,column=0,row=2,sticky='nesw')
        submitButton.bind('<Button-1>',\
                          lambda event:self.openTable(selectTableWin,selectTable))
        """
        We give the user a choice from the list tables pulled from the current
        database. Using a StringVar() object ensures that its value will reflect
        whatever is chosen from the Combobox list. But StringVar() requires an
        accessor to access the current value - notice the use of .get() in the
        method openTable() below. For such an object, get() is a method used
        to access the value of the object and return it.
        
        ###??? Nope, I don't see the .get(). Also, I (ajp) needs a bit of help 
        with this to understand what 'value' is and how the answer is retrieved and
        used? OH, I see the .get() now but it is not in this method. Too confusing. 
        """

    def openTable(self,window,selectTable):
        '''
        Instantiate a selectedTable object.

        Parameters:
        selectTable - Table selected by the user. 
        
        We let the user know we have the table and we enable the buttons (previously 
        disabled) to work with the table.
        '''
        if messagebox.askokcancel('Table selected',selectTable.get() + \
                                  ' has been selected'):
            self.selectedTable = selectTable.get()
            self.buttonAdd.state(['!disabled'])
            self.buttonView.state(['!disabled'])
            self.buttonExport.state(['!disabled'])
            window.destroy()
        """
        Buttons to work with the table have been instantiated in a 'disabled' state.
        Once a table is selected and we have that selection as an object, this method
        'enables' those buttons, so they can be used.
        """

    def add(self):
        '''
        Instantiates a new window and grabs a blank record.
        '''
        # Get a new blank record
        record = self.db.newRecord(self.selectedTable)

        # Set up a window for the new record
        addRecordWin = tkinter.Toplevel(self.frame)
        addRecordWin.title('New Record')
        addRecordWin.geometry('+250+250')
        self.recordWindow(addRecordWin,record)

        """
        This feeds into the next method which builds the window with the information from
        record. We use another instantiation with the method below if we want to show the
        user an existing record that can then be updated.
        """

    def recordWindow(self,window,record,rowid=None):
        '''
        Set up a window listing all the fields from a table and their values if a row is chosen.
        
        Parameters:
        ###??? see convention and list appropriately

        Builds the window with database field names and entries for the values. The data
        type will be checked and appropriate validation required for each type. If it is passed
        a blank record, it displays blank fields. If it is given an existing record, it will
        display that record's fields' values in the entries. We do this by setting a default value
        of None for rowid and checking for the existence of a value as we build the window. 
        ### - nice comment George
        '''
        fieldVar = OrderedDict()
        index = 0
        # work through k (key) and v (value) for each field
        for k, v in record.items():
            # Check if the field autoincrements (id) and if so, we skip it
            if v.autoIncrement:
                pass
            else:
                if v.notNull: # required field, show its label in red
                    label = ttk.Label(window,text='*' + k,foreground='red')
                else:
                    label = ttk.Label(window,text=k)
                # Check if the type is 'date' - if it is, set up a custom entry and validate.
                if str.lower(v.type) == 'date':
                    fieldVar[k] = [k + 'year', k + 'month', k + 'day']
                    label.grid(column=0,row=index+1,sticky="E",padx=3,pady=3)
                    #Set up three fields in the same row for year, month, and date.
                    fieldVar[k][0] = tkinter.StringVar()
                    entryYear = ttk.Entry(window,
                                           textvariable=fieldVar[k][0],width=4)
                    entryYear.grid(column=1,row=index+1,sticky='W',padx=3,pady=3)
                    # Check to see if this is an existing record and extract the value
                    if str(v.value) != 'None':
                        # Take slices of the date value to divide it into separate entries
                        # for year, month and date.
                        year = str(v.value[:4])
                        fieldVar[k][0].set(year)
                    labelYear = ttk.Label(window,text='YYYY')
                    labelYear.grid(column=1,row=index,sticky='W',padx=3,pady=3)
                    fieldVar[k][1] = tkinter.StringVar()
                    entryMonth = ttk.Entry(window,
                                           textvariable=fieldVar[k][1], width=3)
                    entryMonth.grid(column=1,row=index+1,padx=3,pady=3)
                    # Check to see if this is an existing record and extract the value
                    if str(v.value) != 'None':
                        month = str(v.value[4:7])
                        fieldVar[k][1].set(month)
                    labelMonth = ttk.Label(window,text='MMM')
                    labelMonth.grid(column=1,row=index,padx=3,pady=3)
                    fieldVar[k][2] = tkinter.StringVar()
                    entryDay = ttk.Entry(window,
                                           textvariable=fieldVar[k][2], width=2)
                    entryDay.grid(column=1,row=index+1,sticky='E',padx=3,pady=3)
                    # Check to see if this is an existing record and extract the value
                    if str(v.value) != 'None':
                        day = str(v.value[7:])
                        fieldVar[k][2].set(day)
                    labelDay = ttk.Label(window,text='DD')
                    labelDay.grid(column=1,row=index,sticky='E',padx=3,pady=3)
                    index +=2
                #Text data (a string) ###???Are there no numbers?
                else:
                    label.grid(column=0,row=index,sticky='E',padx=3,pady=3)
                    fieldVar[k] = k
                    fieldVar[k] = tkinter.StringVar()
                    entry = ttk.Entry(window, textvariable=fieldVar[k])
                    entry.grid(column=1,row=index,padx=3,pady=3)
                    # Check to see if this is an existing record and extract the value
                    if str(v.value) != 'None':
                        defaultText = v.value
                        fieldVar[k].set(defaultText)
                    index += 1
        
        ###???what is happening here?
        submitButton = ttk.Button(window,text="Submit")
        submitButton.grid(column=1,row=index,padx=3,pady=3)
        submitButton.bind('<Button-1>',\
                              lambda event:self.add2(record,fieldVar,window,rowid))
        cancelButton = ttk.Button(window,text='Cancel')
        cancelButton.grid(column=0,row=index,padx=3,pady=3)
        cancelButton.bind('<Button-1>',lambda event:self.cancel(window))
        if rowid: ###??? and here? - keep in mind, the pedagogy comments do not replace the programmer comments which should briefly explain what you are doing or explain tricky things
            deleteButton = ttk.Button(window,text='Delete',\
                                      command=lambda :self.delete(window,rowid))
            deleteButton.grid(column=1,row=index+1,padx=3,pady=3)

        """
        We pass a record to the method. If there are no existing values (a blank record)
        we use the field names as labels and leave the entries blank. If there are values
        we fill each entry with those values. We use an ordered dictionary that we build
        with each field in the record to keep track of the variables for the fields so we
        can extract them later. ###??? the order is because...
        
        First we check if the field is set to autoincrement, and if
        so we skip that field - it should not be modified or initiated by the user. ###??? I believe it can, so we should probably say "should not"
        
        If the field
        is a date, we make the value for that entry a list with three entries to track the
        three elements of a date: year, month and day. We set the keys in the dictionary to
        the field names and make the corresponding value also that field name, except for
        dates. For a date, we take the three fields and combine them into one text string.
        Each value is then declared a StringVar variable which changes when the entry
        changes. 
        
        This dictionary is passed to the next method when the submit button is  ###??? next method, what if someone changes the order of the methods, why not use its name, and if it is add2, why that name?
        pressed in order to extract the data the user has entered.
        """

    def add2(self,record,fieldVar,win,rowid):
        '''
        Process to extract the user's data, check it, and insert it into the record.
        ###??? Is this only for add or do you use it for update too? Maybe a different name for this method
        
        Parameters:
        ###??? add them please

        Data is extracted from fieldVar dictionary and inserted into record. Then any date
        field is validated and if deficient, the original window is repopulated with the
        user's data with an appropriate message.
        '''
        commit = True # assume data is good unless you find otherwise
        #work through the dictionary's k(key) and v(value)
        for k,v in record.items():
            # Check if data type is date and has an entry, which needs separate insertion method
            if str.lower(v.type) == 'date' and fieldVar[k][0].get(): # a date was given
                record.add(k,fieldVar[k][0].get()+ fieldVar[k][1].get().capitalize()+ fieldVar[k][2].get())
                # Validate the year
                if not validate.dateYear(fieldVar[k][0].get()):
                    messagebox.showinfo(message='Please enter a valid year using 4 digits.')
                    commit = False
                # Validate the month
                elif not validate.dateMonth(fieldVar[k][1].get()):
                    messagebox.showinfo(message='Please enter 3 letters to indicate a valid month.')
                    commit = False
                # Validate the day
                elif not validate.dateDay(fieldVar[k][2].get(),fieldVar[k][1].get(),fieldVar[k][0].get()):
                    messagebox.showinfo(message='Please enter a valid day for this month and year.')
                    commit = False
            # Pass on an empty date field since it is handled differently
            elif str.lower(v.type) == 'date':
                pass
            # Pass on an autoincremented field since we don't want the user to change it
            elif v.autoIncrement:
                pass
            # If field should not be empty, don't leave it empty
            elif v.notNull and not fieldVar[k].get():
                commit = False
                messagebox.showinfo(message=k + ' cannot be empty')
            # All other data is inserted
            else:
                record.add(k,fieldVar[k].get())
        ###??? Let's think about dates again. This seems pretty complex. 

        # If data not valid, rebuild the window with user's data for corrections
        if not commit:
            self.recordWindow(win,record,rowid)
        # If data is valid, send the record to the database object to be entered in the database, close
        # the window.
        else:
            if rowid:
                self.db.updateRecord(record,self.selectedTable,rowid)
            else:
                self.db.addRecord(record,self.selectedTable)
            win.destroy()

        """
        We extract the data by calling the .get() method on the variables we have established in the
        fieldVar dictionary. Since we have two ordered dictionaries, there is a one to one correspondence
        on the items in the dictionaries and we can insert the data into the record object using the .add()
        method for a record. For a date field, we check if it is empty. If so, we pass, or else the user
        will have to enter a date. Otherwise, we concatenate all three elements in the correct order so
        we can extract it again as necessary. We validate the dates using the validate.py module. If a date
        is invalid in some way, we reinstantiate the record window with the data the user has given us
        after we give the user a message as to what needs changing. We use this same method when we check
        for NOT NULL. This validation needs to be moved to the validation module somehow.
        """
        
        ###???AJP stopped here - need to think about the date handling and data structures a bit
            

    def view(self,rowid):
        '''
        Instantiates a new window and grabs an existing record identified by rowid.

        This record and window are passed to recordWindow() in order to build a window using an existing
        record.
        '''
        record = self.db.viewRecord(self.selectedTable,rowid)
        viewRecordWin = tkinter.Toplevel(self.frame)
        viewRecordWin.title('Record')
        viewRecordWin.geometry('+250+250')
        self.recordWindow(viewRecordWin,record,rowid)

        """
        This method gets the rowid from selectRow(). Then it passes the window, record and rowid to
        recordWindow().
        """

    def selectRow(self):
        '''
        Method to return a rowid from two field values chosen by the user.

        Gives the user sequential choices to choose the values for two fields in order to identify a row.
        Returns the rowid based on those choices. User first chooses the field then the value, then the
        second field and then that value.
        '''

        # Set up the window
        selectRecordWin = tkinter.Toplevel(self.frame)
        selectRecordWin.geometry('450x150+150+150')
        selectRecordWin.title('Choose Record')
        # Set up the first label
        winLbl = ttk.Label(selectRecordWin,text='Select two fields in order and their values to identify the record.')
        winLbl.grid(column=0,row=0,columnspan=3)
        # Get a list of all field names from the table
        colNameList = self.db.selectColumn(self.selectedTable)
        field1Lbl = ttk.Label(selectRecordWin,text='First Field')
        field1Lbl.grid(column=0,row=1,sticky='E')
        field1 = tkinter.StringVar()
        # Give the user a combobox from which he can only choose existing fields in the database
        field1List = ttk.Combobox(selectRecordWin,width=20,textvariable=field1,state='readonly')
        field1List['values'] = colNameList
        field1List.grid(column=1,row=1)      
        def selectValue():
            '''
            Gives the user a choice of values from the first field.
            
            Triggered by a choice in the first combobox.
            '''
            field1Value = tkinter.StringVar()
            field1Choice = ttk.Combobox(selectRecordWin,width=20,textvariable=field1Value,state='readonly')
            # Get a list of possible values for the first field
            field1Choices = self.db.selectValue(self.selectedTable,field1.get())
            field1Choice['values'] = field1Choices
            field1Choice.grid(column=2,row=1)
            def selectField2():
                '''
                Gives the user a choice of the second field.

                Triggered by a choice in the second combobox.
                '''
                # Remove the first field chosen from the list of choices of fields
                colNameList = self.db.selectColumn(self.selectedTable)
                colNameList.remove(field1.get())
                field2Lbl = ttk.Label(selectRecordWin,text='Second Field')
                field2Lbl.grid(column=0,row=2,sticky='E')
                field2 = tkinter.StringVar()
                field2List = ttk.Combobox(selectRecordWin,width=20,textvariable=field2,state='readonly')
                field2List['values'] = colNameList
                field2List.grid(column=1,row=2)
                def selectValue2():
                    '''
                    Gives the user a choice of values from the second field.
                    
                    Triggered by a choice in the third combobox.
                    '''
                    field2Value = tkinter.StringVar()
                    field2Choice = ttk.Combobox(selectRecordWin,width=20,textvariable=field2Value,state='readonly')
                    # Get a list of possible values for the second field based on choice of first field value
                    field2Choices = self.db.selectValue2(self.selectedTable,field2.get(),field1.get(),field1Value.get())
                    field2Choice['values'] = field2Choices
                    field2Choice.grid(column=2,row=2)
                    def submitButton():
                        '''
                        Sets up submit button

                        Triggered by a choice in the fourth combobox.
                        '''
                        buttonSubmit = ttk.Button(selectRecordWin,text="Submit")
                        buttonSubmit.grid(column=1,row=3)
                        def submit():
                            '''
                            Calculates rowid from values chosen by user

                            Triggered by clicking on submit button.
                            '''
                            rowid = self.db.getRowid(self.selectedTable,field1.get(),field1Value.get(),\
                                                     field2.get(),field2Value.get())
                            self.view(rowid)
                            selectRecordWin.destroy()
                        # Call submit method when submit button is pressed
                        buttonSubmit.bind('<Button-1>', lambda event: submit())
                    # Call method to et up submit button when second field value is chosen
                    field2Choice.bind('<<ComboboxSelected>>', lambda event:submitButton())
                # Call method to set up combobox list of choices of values for second field when second field is chosen
                field2List.bind('<<ComboboxSelected>>', lambda event:selectValue2())
            # Call method to set up combobox list of choices for second field when first field value is chosen
            field1Choice.bind('<<ComboboxSelected>>', lambda event:selectField2())
        # Call method to set up combobox list of choices of values for first field when first field is chosen
        field1List.bind('<<ComboboxSelected>>', lambda event:selectValue())
        '''
        We use nested functions to instantiate sequential choices for the user as he or she makes each choice. This makes
        it possible to narrow down the record desired. With a combobox list, we are able to bind the combobox to a
        method when a choice is made in the list (i.e., the user clicks on a choice). Thus each choice leads to the
        next one, with appropriate possibilities given each time based on the last choice. The binding takes place under
        the nested function being called, so that Python is already aware of the function it is calling. At the end, we give
        the user a submit button in order to finalize his or her choice of record.

        We could have used separate methods in our database object and called them sequentially. However, they are designed
        to be sequential and used only in this method, so we chose to nest them inside this method so as to keep it all local
        to the method. Using nesting, there was no need to pass in needed variables that had been already used in the method
        (or preceding functions).
        '''

    def delete(self,win,rowid):
        '''
        Given a row to delete, verify with the user, then delete the row.
        '''
        if not messagebox.askokcancel(title='DELETE RECORD?',\
                                      message='Do you really want to DELETE this record?\n' +\
                                      'Once deleted it cannot be recovered!',\
                                      icon='question'):
            return
        success = self.db.deleteRecord(self.selectedTable,rowid)
        if success:
            messagebox.showinfo(title='Record deleted',\
                                message='You have deleted the selected record!')
        win.destroy()
        
        """
        This method is called by clicking on the delete button after selecting a record.
        We use the same method to select the record as for updating or viewing a record.
        The delete button will only appear if a record has been selected - not for a new
        record. Once the user has confirmed that s/he wants to delete the record, we
        pass the rowid to the delete method of the database and then destroy the window.
        """
    def export(self):
        fileName = filedialog.asksaveasfilename(title='File to which to export',defaultextension='.csv',\
                                                  parent=self.frame)
        if fileName:
            self.db.exportTable(fileName,self.selectedTable)
            messagebox.showinfo(message='You exported ' + self.selectedTable + \
                            ' to ' + fileName)

    def cancel(self,win):
        if messagebox.askokcancel(message='Do you really want to cancel adding/changing this record?'):
            win.destroy()
                                                 
    def close(self):
        '''
        Close our program graciously by asking confirmation to close. Then it calls the
        function to close the connection to the database (if it exists) before closing
        the window.
        
        Handler is tied to "Exit" button as well as "X" on window.
        '''
        if messagebox.askokcancel("Quit", "Do you really wish to quit?"):
            if self.connected:
                self.db.closeDB()
            self.root.destroy()
        """
        This gives the user a chance to confirm that s/he actually wants to 
        quit the program, instead of simply destroying the window. The tkinter 
        messagebox module had to be imported specifically so this function 
        would work from the command line (this worked in IDLE without 
        specifically importing because IDLE is a tkinter environment - apparently 
        some modules are not automatically imported with tkinter). This particular problem is
        referenced here:
        http://stackoverflow.com/questions/20074007/python-3-2-script-not-working-and-or-importing-
        tkinter-when-running-from-linux-d
        """

        
def main():
    editor = DBgui()
    editor.root.mainloop()

if __name__=='__main__':
    main()
